/*
 Navicat Premium Data Transfer

 Source Server         : phonebook
 Source Server Type    : MySQL
 Source Server Version : 50510
 Source Host           : localhost
 Source Database       : assg2

 Target Server Type    : MySQL
 Target Server Version : 50510
 File Encoding         : utf-8

 Date: 04/22/2011 17:33:50 PM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `ACT`
-- ----------------------------
DROP TABLE IF EXISTS `ACT`;
CREATE TABLE `ACT` (
  `MOVIE_idmovie` int(11) NOT NULL,
  `ACTOR_idactor` int(11) NOT NULL,
  PRIMARY KEY (`MOVIE_idmovie`,`ACTOR_idactor`),
  KEY `fk_MOVIE_has_ACTOR_ACTOR1` (`ACTOR_idactor`),
  KEY `fk_MOVIE_has_ACTOR_MOVIE1` (`MOVIE_idmovie`),
  CONSTRAINT `fk_MOVIE_has_ACTOR_ACTOR1` FOREIGN KEY (`ACTOR_idactor`) REFERENCES `ACTOR` (`idactor`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_MOVIE_has_ACTOR_MOVIE1` FOREIGN KEY (`MOVIE_idmovie`) REFERENCES `MOVIE` (`idmovie`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `ACTOR`
-- ----------------------------
DROP TABLE IF EXISTS `ACTOR`;
CREATE TABLE `ACTOR` (
  `idactor` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) NOT NULL,
  `lastname` varchar(45) NOT NULL,
  PRIMARY KEY (`idactor`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `ACTOR`
-- ----------------------------
BEGIN;
INSERT INTO `ACTOR` VALUES ('1', 'Cameron', 'Diaz'), ('2', 'Johnny', 'Depp');
COMMIT;

-- ----------------------------
--  Table structure for `AMENITIE`
-- ----------------------------
DROP TABLE IF EXISTS `AMENITIE`;
CREATE TABLE `AMENITIE` (
  `idamenitie` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`idamenitie`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `BOOK`
-- ----------------------------
DROP TABLE IF EXISTS `BOOK`;
CREATE TABLE `BOOK` (
  `VIEWER_idviewer` int(11) NOT NULL,
  `SHOW_MOVIE_idmovie` int(11) NOT NULL,
  `SHOW_ROOM_idroom` int(11) NOT NULL,
  `SHOW_ROOM_CINEMA_idcinema` int(11) NOT NULL,
  `numberofseats` int(11) NOT NULL,
  PRIMARY KEY (`VIEWER_idviewer`,`SHOW_MOVIE_idmovie`,`SHOW_ROOM_idroom`,`SHOW_ROOM_CINEMA_idcinema`),
  KEY `fk_VIEWER_has_SHOW_SHOW1` (`SHOW_MOVIE_idmovie`,`SHOW_ROOM_idroom`,`SHOW_ROOM_CINEMA_idcinema`),
  KEY `fk_VIEWER_has_SHOW_VIEWER1` (`VIEWER_idviewer`),
  CONSTRAINT `fk_VIEWER_has_SHOW_SHOW1` FOREIGN KEY (`SHOW_MOVIE_idmovie`, `SHOW_ROOM_idroom`, `SHOW_ROOM_CINEMA_idcinema`) REFERENCES `SHOW` (`MOVIE_idmovie`, `ROOM_idroom`, `ROOM_CINEMA_idcinema`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_VIEWER_has_SHOW_VIEWER1` FOREIGN KEY (`VIEWER_idviewer`) REFERENCES `VIEWER` (`idviewer`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `CINEMA`
-- ----------------------------
DROP TABLE IF EXISTS `CINEMA`;
CREATE TABLE `CINEMA` (
  `idcinema` int(11) NOT NULL AUTO_INCREMENT,
  `location` varchar(45) DEFAULT NULL,
  `OWNER_idowner` int(11) NOT NULL,
  PRIMARY KEY (`idcinema`),
  KEY `fk_CINEMA_OWNER1` (`OWNER_idowner`),
  CONSTRAINT `fk_CINEMA_OWNER1` FOREIGN KEY (`OWNER_idowner`) REFERENCES `OWNER` (`idowner`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `FAVOURITE_ACTOR`
-- ----------------------------
DROP TABLE IF EXISTS `FAVOURITE_ACTOR`;
CREATE TABLE `FAVOURITE_ACTOR` (
  `VIEWER_idviewer` int(11) NOT NULL,
  `ACTOR_idactor` int(11) NOT NULL,
  PRIMARY KEY (`VIEWER_idviewer`,`ACTOR_idactor`),
  KEY `fk_VIEWER_has_ACTOR_ACTOR1` (`ACTOR_idactor`),
  KEY `fk_VIEWER_has_ACTOR_VIEWER1` (`VIEWER_idviewer`),
  CONSTRAINT `fk_VIEWER_has_ACTOR_ACTOR1` FOREIGN KEY (`ACTOR_idactor`) REFERENCES `ACTOR` (`idactor`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_VIEWER_has_ACTOR_VIEWER1` FOREIGN KEY (`VIEWER_idviewer`) REFERENCES `VIEWER` (`idviewer`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `FAVOURITE_GENRE`
-- ----------------------------
DROP TABLE IF EXISTS `FAVOURITE_GENRE`;
CREATE TABLE `FAVOURITE_GENRE` (
  `VIEWER_idviewer` int(11) NOT NULL,
  `GENRE_idgenre` int(11) NOT NULL,
  PRIMARY KEY (`VIEWER_idviewer`,`GENRE_idgenre`),
  KEY `fk_VIEWER_has_GENRE_GENRE1` (`GENRE_idgenre`),
  KEY `fk_VIEWER_has_GENRE_VIEWER1` (`VIEWER_idviewer`),
  CONSTRAINT `fk_VIEWER_has_GENRE_GENRE1` FOREIGN KEY (`GENRE_idgenre`) REFERENCES `GENRE` (`idgenre`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_VIEWER_has_GENRE_VIEWER1` FOREIGN KEY (`VIEWER_idviewer`) REFERENCES `VIEWER` (`idviewer`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `GENRE`
-- ----------------------------
DROP TABLE IF EXISTS `GENRE`;
CREATE TABLE `GENRE` (
  `idgenre` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`idgenre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `LIST_AMENITIES`
-- ----------------------------
DROP TABLE IF EXISTS `LIST_AMENITIES`;
CREATE TABLE `LIST_AMENITIES` (
  `AMENITIE_idamenitie` int(11) NOT NULL,
  `CINEMA_idcinema` int(11) NOT NULL,
  PRIMARY KEY (`AMENITIE_idamenitie`,`CINEMA_idcinema`),
  KEY `fk_AMENITIE_has_CINEMA_CINEMA1` (`CINEMA_idcinema`),
  KEY `fk_AMENITIE_has_CINEMA_AMENITIE1` (`AMENITIE_idamenitie`),
  CONSTRAINT `fk_AMENITIE_has_CINEMA_AMENITIE1` FOREIGN KEY (`AMENITIE_idamenitie`) REFERENCES `AMENITIE` (`idamenitie`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_AMENITIE_has_CINEMA_CINEMA1` FOREIGN KEY (`CINEMA_idcinema`) REFERENCES `CINEMA` (`idcinema`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `MOVIE`
-- ----------------------------
DROP TABLE IF EXISTS `MOVIE`;
CREATE TABLE `MOVIE` (
  `idmovie` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(45) NOT NULL,
  `poster` varchar(45) DEFAULT NULL,
  `synopsis` text,
  `agerating` varchar(45) DEFAULT NULL,
  `GENRE_idgenre` int(11) NOT NULL,
  PRIMARY KEY (`idmovie`),
  KEY `fk_MOVIE_GENRE1` (`GENRE_idgenre`),
  CONSTRAINT `fk_MOVIE_GENRE1` FOREIGN KEY (`GENRE_idgenre`) REFERENCES `GENRE` (`idgenre`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `OWNER`
-- ----------------------------
DROP TABLE IF EXISTS `OWNER`;
CREATE TABLE `OWNER` (
  `idowner` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) NOT NULL,
  `lastname` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `username` varchar(45) NOT NULL,
  `email` varchar(45) NOT NULL,
  PRIMARY KEY (`idowner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `REVIEW`
-- ----------------------------
DROP TABLE IF EXISTS `REVIEW`;
CREATE TABLE `REVIEW` (
  `VIEWER_idviewer` int(11) NOT NULL,
  `MOVIE_idmovie` int(11) NOT NULL,
  `dateposted` date NOT NULL,
  `description` varchar(45) DEFAULT NULL,
  `rating` int(11) DEFAULT NULL,
  PRIMARY KEY (`VIEWER_idviewer`,`MOVIE_idmovie`),
  KEY `fk_VIEWER_has_MOVIE_MOVIE1` (`MOVIE_idmovie`),
  KEY `fk_VIEWER_has_MOVIE_VIEWER` (`VIEWER_idviewer`),
  CONSTRAINT `fk_VIEWER_has_MOVIE_MOVIE1` FOREIGN KEY (`MOVIE_idmovie`) REFERENCES `MOVIE` (`idmovie`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_VIEWER_has_MOVIE_VIEWER` FOREIGN KEY (`VIEWER_idviewer`) REFERENCES `VIEWER` (`idviewer`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `ROOM`
-- ----------------------------
DROP TABLE IF EXISTS `ROOM`;
CREATE TABLE `ROOM` (
  `idroom` int(11) NOT NULL AUTO_INCREMENT,
  `numberofseats` varchar(45) NOT NULL,
  `CINEMA_idcinema` int(11) NOT NULL,
  PRIMARY KEY (`idroom`,`CINEMA_idcinema`),
  KEY `fk_ROOM_CINEMA1` (`CINEMA_idcinema`),
  CONSTRAINT `fk_ROOM_CINEMA1` FOREIGN KEY (`CINEMA_idcinema`) REFERENCES `CINEMA` (`idcinema`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `SHOW`
-- ----------------------------
DROP TABLE IF EXISTS `SHOW`;
CREATE TABLE `SHOW` (
  `starts` datetime NOT NULL,
  `finish` datetime NOT NULL,
  `MOVIE_idmovie` int(11) NOT NULL,
  `ROOM_idroom` int(11) NOT NULL,
  `ROOM_CINEMA_idcinema` int(11) NOT NULL,
  PRIMARY KEY (`MOVIE_idmovie`,`ROOM_idroom`,`ROOM_CINEMA_idcinema`),
  KEY `fk_SHOW_MOVIE1` (`MOVIE_idmovie`),
  KEY `fk_SHOW_ROOM1` (`ROOM_idroom`,`ROOM_CINEMA_idcinema`),
  CONSTRAINT `fk_SHOW_MOVIE1` FOREIGN KEY (`MOVIE_idmovie`) REFERENCES `MOVIE` (`idmovie`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_SHOW_ROOM1` FOREIGN KEY (`ROOM_idroom`, `ROOM_CINEMA_idcinema`) REFERENCES `ROOM` (`idroom`, `CINEMA_idcinema`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `VIEWER`
-- ----------------------------
DROP TABLE IF EXISTS `VIEWER`;
CREATE TABLE `VIEWER` (
  `idviewer` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `nickname` varchar(45) DEFAULT NULL,
  `firstname` varchar(45) NOT NULL,
  `lastname` varchar(45) NOT NULL,
  `email` varchar(45) NOT NULL,
  `yearofbirth` date NOT NULL,
  PRIMARY KEY (`idviewer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;
